"""
Case Type   : 事件触发器
Case Name   : 【事件触发器】CREATE/ALTER/DROP USER MAPPING命令触发事件触发器
Create At   :2024/07/08
Owner       :sungang14
Description :
    预置条件：
        1.创建事件触发器：
        CREATE OR REPLACE FUNCTION f_eventtrigger_0011()
        RETURNS event_trigger
        LANGUAGE plpgsql
        AS $$ BEGIN
        RAISE NOTICE 'test_event_trigger: % %', tg_event, tg_tag;
        END;
        $$;
        CREATE EVENT TRIGGER start_eventtri ON ddl_command_start EXECUTE PROCEDURE f_eventtrigger_0011();
        CREATE EVENT TRIGGER end_eventtri ON ddl_command_end EXECUTE PROCEDURE f_eventtrigger_0011();
        CREATE EVENT TRIGGER drop_eventtri ON sql_drop EXECUTE PROCEDURE f_eventtrigger_0011();
        CREATE EVENT TRIGGER rewrite_eventtri ON  table_rewrite EXECUTE PROCEDURE f_eventtrigger_0011(); 
       2、创建表
        CREATE TABLE t_eventtrigger_0011(id int, col int);
    测试步骤：
        1、create USER MAPPING v_eventtrigger_0011 as select * from t_eventtrigger_0011;
        2、ALTER USER MAPPING v_eventtrigger_0011 RENAME TO newt_v_eventtrigger_0011;
        3、DROP USER MAPPING newt_v_eventtrigger_0011;
Expect      : 
    1、触发执行函数f_eventtrigger_0011()
        test_event_trigger: ddl_command_start,CREATE USER MAPPING
        test_event_trigger: ddl_command_end,CREATE USER MAPPING
    2、触发执行函数f_eventtrigger_0011()
        test_event_trigger: ddl_command_start,ALTER USER MAPPING
        test_event_trigger: ddl_command_end,ALTER USER MAPPING
    3、触发执行函数f_eventtrigger_0011()
        test_event_trigger: ddl_command_start,DROP USER MAPPING
        test_event_trigger: sql_drop,DROP USER MAPPING
        test_event_trigger: ddl_command_end,DROP USER MAPPING
History     : 
"""

import sys
import os
import unittest
from yat.test import Node
from yat.test import macro

sys.path.append(sys.path[0] + "/../")
from testcase.utils.Logger import Logger
from testcase.utils.Common import Common
from testcase.utils.CommonSH import CommonSH


class IndexFileDamaged(unittest.TestCase):
    def setUp(self):
        self.logger = Logger()
        self.logger.info(f'-----{os.path.basename(__file__)} start-----')
        self.logger.info('----------------this is setup-----------------------')
        self.DB_ENV_PATH = macro.DB_ENV_PATH
        self.DB_INSTANCE_PATH = macro.DB_INSTANCE_PATH
        self.comsh = CommonSH('PrimaryDbBmode')
        self.comsh.execut_db_sql("drop EVENT TRIGGER if exists start_eventtri;")
        self.comsh.execut_db_sql("drop EVENT TRIGGER if exists end_eventtri;")
        self.comsh.execut_db_sql("drop EVENT TRIGGER if exists drop_eventtri;")
        self.comsh.execut_db_sql("drop EVENT TRIGGER if exists rewrite_eventtri;")
        self.comsh.execut_db_sql("drop function if exists f_eventtrigger_0011();")
        self.comsh.execut_db_sql('DROP USER if exists um_eventtrigger_0011;')
        self.comsh.execut_db_sql('DROP extension  if exists postgres_fdw;')
        self.comsh.execut_db_sql('DROP TABLE if exists t_eventtrigger_0011;')
        msg = self.comsh.execut_db_sql("CREATE TABLE t_eventtrigger_0011(id int, col int);")
        self.logger.info(msg)
        self.assertEqual("CREATE TABLE", msg)
        self.comsh.execut_db_sql("insert into t_eventtrigger_0011 values(1,1);")
        gs_guc_cmd = f"gs_guc generate -o usermapping -S default -D $GAUSSHOME/bin"
        ret_msg = self.comsh.exec_gs_ssh(gs_guc_cmd)
        self.logger.info(ret_msg)
        self.assertIn("SUCCESS", ret_msg)
        self.assertNotIn("FAILED", ret_msg)
        msg = self.comsh.execut_db_sql('create extension postgres_fdw;')
        self.logger.info(msg)
        self.assertIn("CREATE EXTENSION", msg)
        msg = self.comsh.execut_db_sql(
            "create server s_eventtrigger_0011 foreign data wrapper postgres_fdw options (host '172.16.103.82',port '64322',dbname 'postgres');")
        self.logger.info(msg)
        self.assertIn("CREATE SERVER", msg)
        msg = self.comsh.execut_db_sql("CREATE USER um_eventtrigger_0011 password 'Huawei@123';")
        self.assertIn("CREATE ROLE", msg)
        self.logger.info(msg)
        msg = self.comsh.execut_db_sql(
            f"CREATE OR REPLACE FUNCTION f_eventtrigger_0011() RETURNS event_trigger LANGUAGE plpgsql" \
            f" AS \$\$ BEGIN" \
            f" RAISE NOTICE 'test_event_trigger: % %', tg_event, tg_tag;" \
            f"END;" \
            f"\$\$;")
        self.logger.info(msg)
        self.assertEqual("CREATE FUNCTION", msg)
        msg = self.comsh.execut_db_sql(
            "CREATE EVENT TRIGGER start_eventtri ON ddl_command_start EXECUTE PROCEDURE f_eventtrigger_0011();")
        self.logger.info(msg)
        self.assertEqual("CREATE EVENT TRIGGER", msg)
        msg = self.comsh.execut_db_sql(
            "CREATE EVENT TRIGGER end_eventtri ON ddl_command_end EXECUTE PROCEDURE f_eventtrigger_0011();")
        self.logger.info(msg)
        self.assertEqual("CREATE EVENT TRIGGER", msg)
        msg = self.comsh.execut_db_sql(
            "CREATE EVENT TRIGGER drop_eventtri ON sql_drop EXECUTE PROCEDURE f_eventtrigger_0011();")
        self.logger.info(msg)
        self.assertEqual("CREATE EVENT TRIGGER", msg)
        msg = self.comsh.execut_db_sql(
            "CREATE EVENT TRIGGER rewrite_eventtri ON table_rewrite EXECUTE PROCEDURE f_eventtrigger_0011();")
        self.logger.info(msg)
        self.assertEqual("CREATE EVENT TRIGGER", msg)

    def test_Index_file_damaged(self):
        self.logger.info(
            "创建USER MAPPING:create user MAPPING FOR um_eventtrigger_0011 SERVER s_eventtrigger_0011 OPTIONS (user 'hzy',password '123456Aa');")
        msg = self.comsh.execut_db_sql(
            "create user MAPPING FOR um_eventtrigger_0011 SERVER s_eventtrigger_0011 OPTIONS (user 'hzy',password '123456Aa');")
        self.logger.info(msg)
        self.assertIn(f'CREATE USER MAPPING', msg)
        self.assertIn(f'test_event_trigger: ddl_command_start CREATE USER MAPPING', msg)
        self.assertIn(f'test_event_trigger: ddl_command_end CREATE USER MAPPING', msg)

        self.logger.info(
            "修改USER MAPPING:ALTER USER MAPPING FOR um_eventtrigger_0011 SERVER s_eventtrigger_0011 OPTIONS (SET password 'public');")
        msg = self.comsh.execut_db_sql(
            "ALTER USER MAPPING FOR um_eventtrigger_0011 SERVER s_eventtrigger_0011 OPTIONS (SET password 'public');")
        self.logger.info(msg)
        self.assertIn(f'test_event_trigger: ddl_command_start ALTER USER MAPPING', msg)
        self.assertIn(f'test_event_trigger: ddl_command_end ALTER USER MAPPING', msg)
        self.assertIn("ALTER USER MAPPING", msg)

        self.logger.info('删除USER MAPPING:DROP USER MAPPING FOR um_eventtrigger_0011 SERVER s_eventtrigger_0011;')
        msg = self.comsh.execut_db_sql('DROP USER MAPPING FOR um_eventtrigger_0011 SERVER s_eventtrigger_0011;')
        self.logger.info(msg)
        self.assertIn(f'test_event_trigger: ddl_command_start DROP USER MAPPING', msg)
        self.assertIn(f'test_event_trigger: sql_drop DROP USER MAPPING', msg)
        self.assertIn(f'test_event_trigger: ddl_command_end DROP USER MAPPING', msg)
        self.assertIn("DROP USER MAPPING", msg)

    def tearDown(self):
        self.logger.info('----------------this is tearDown-----------------------')
        # 删除事件触发器
        msg = self.comsh.execut_db_sql("drop EVENT TRIGGER if exists start_eventtri;")
        self.logger.info(msg)
        self.assertEqual('DROP EVENT TRIGGER', msg)
        msg = self.comsh.execut_db_sql("drop EVENT TRIGGER if exists end_eventtri;")
        self.logger.info(msg)
        self.assertEqual('DROP EVENT TRIGGER', msg)
        msg = self.comsh.execut_db_sql("drop EVENT TRIGGER if exists drop_eventtri;")
        self.logger.info(msg)
        self.assertEqual('DROP EVENT TRIGGER', msg)
        msg = self.comsh.execut_db_sql("drop EVENT TRIGGER if exists rewrite_eventtri;")
        self.logger.info(msg)
        self.assertEqual('DROP EVENT TRIGGER', msg)
        # 删除事件触发器函数
        msg = self.comsh.execut_db_sql("drop function if exists f_eventtrigger_0011();")
        self.logger.info(msg)
        self.assertEqual('DROP FUNCTION', msg)
        # 删除表
        msg = self.comsh.execut_db_sql('DROP TABLE if exists t_eventtrigger_0011;')
        self.logger.info(msg)
        self.assertIn('DROP TABLE', msg)
        msg = self.comsh.execut_db_sql('DROP server  if exists s_eventtrigger_0011 ;')
        self.logger.info(msg)
        self.assertIn("DROP SERVER", msg)
        msg = self.comsh.execut_db_sql("DROP USER  if exists um_eventtrigger_0011;")
        self.assertIn("DROP ROLE", msg)
        msg = self.comsh.execut_db_sql('DROP extension  if exists postgres_fdw ;')
        self.logger.info(msg)
        self.assertIn("DROP EXTENSION", msg)
        self.logger.info(f'-----{os.path.basename(__file__)} end-----')
